package com.pcbly.web.persistence.jbpm;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import org.hibernate.Session;
import org.hibernate.engine.SessionImplementor;

import com.pcbly.web.persistence.HibernateBaseDao;

@SuppressWarnings("unchecked")
public class JbpmHibernateDao extends HibernateBaseDao implements JbpmDao {

	/**
	 * 
	 */
	private static final long serialVersionUID = 522109611757316052L;

	public JbpmHibernateDao() {
		super();
		// TODO Auto-generated constructor stub
	}

	@Override
	public String reportOverallActivity() throws Exception {
		// TODO Auto-generated method stub
		StringBuilder buff = new StringBuilder("<graph showNames=\"");
		buff.append("Overall Activity\" decimalPrecision=\"2\">");
		Session session = getSession();
		Connection conn = ((SessionImplementor) session).getJDBCContext()
				.borrowConnection();
		Statement state = conn.createStatement();
		StringBuilder sql = new StringBuilder("SELECT d.DBID_ AS dpl");
		sql.append(", p.STRINGVAL_ AS processId   ");
		sql.append(" FROM JBPM4_DEPLOYMENT d LEFT  ");
		sql.append(" JOIN JBPM4_DEPLOYPROP p ON   ");
		sql.append(" d.DBID_=p.DEPLOYMENT_   ");
		sql.append("  WHERE p.KEY_='pdid'   ");
		sql.append("    GROUP BY dpl,processId");
		ResultSet rs = state.executeQuery(sql.toString());
		while (rs.next()) {
			buff.append("<set name=\"");
			buff.append(rs.getString("dpl"));
			buff.append("\" value=\"");
			buff.append(rs.getString("processId"));
			buff.append("\"/>");
		}
		if(rs!=null){
			rs.close();
		}
		if(state!=null){
			state.close();
		}
		if(conn!=null){
			conn.close();
		}
		buff.append("</graph>");
		return buff.toString();
	}

	@Override
	public String reportMostActiveProcess() throws Exception {
		// TODO Auto-generated method stub
		StringBuilder buff = new StringBuilder("<graph showNames=\"");
		buff.append("Most Active Process\" decimalPrecision=\"2\">");
		Session session = getSession();
		Connection conn = ((SessionImplementor) session).getJDBCContext()
				.borrowConnection();
		Statement state = conn.createStatement();
		
		StringBuilder sql = new StringBuilder("select d.STRINGVAL_ as id");
		sql.append(", count(p.ID_) as num   ");
		sql.append(" from JBPM4_DEPLOYPROP d left   ");
		sql.append(" join JBPM4_HIST_PROCINST p ON   ");
		sql.append(" d.STRINGVAL_=p.PROCDEFID_   ");
		sql.append("  where d.KEY_='pdid'   ");
		sql.append("    GROUP BY  d.STRINGVAL_ ");
		ResultSet rs = state.executeQuery(sql.toString());

        while (rs.next()) {
            buff.append("<set name=\"");
			buff.append(rs.getString("id"));
			buff.append("\" value=\"");
			buff.append(rs.getString("num"));
			buff.append("\"/>");
        }
        if(rs!=null){
			rs.close();
		}
		if(state!=null){
			state.close();
		}
		if(conn!=null){
			conn.close();
		}
		buff.append("</graph>");
		return buff.toString();
	}

}
